<?php

/**
 * Description of model_karyawan
 *
 * @author dian
 */
class model_karyawan extends Default_My_Model {

    function __construct() {
        parent::__construct();
    }

    function list_karyawan($whr = '') {
        $this->db->select('nik, nama');
        $this->db->from('mt_karyawan');
        if ($whr != '') {
            $this->db->where($whr);
        }
        $result = $this->db->get()->result();
        $arr_res = array(-1 => "---Pilih Karyawan---");

        foreach ($result as $row) {
            $arr_res[$row->nik] = $row->nama;
        }

        return $arr_res;
    }

    function list_vessel() {
        $arr_res = array(-1 => "---Pilih Vessel---");

        $this->db->select('id_vessel, nama_vessel');
        $this->db->from('master_vessel');
        if ($this->userauth->getUserId() == 'super_admin') {
            $arr_res[-2] = "Karyawan Kantor";
        }
        $result = $this->db->get()->result();
        foreach ($result as $row) {
            $arr_res[$row->id_vessel] = $row->nama_vessel;
        }

        return $arr_res;
    }

    function list_absensi($thn_bln, $nik) {
        return $this->db->query($this->get_query_absensi($thn_bln, $nik));
    }

    function get_query_absensi($thn_bln, $nik) {
        return "SELECT da.*, tl.catatan, tl.prosentase_gaji_pokok
                FROM data_absensi da 
                LEFT JOIN tipe_lembur tl on da.tipe_lembur = tl.id_tipe
                WHERE nik = '" . $nik . "' AND tanggal LIKE '" . $thn_bln . "%'
                ORDER BY tanggal";
    }

    function list_lembur() {
        $result = $this->db->query("SELECT * FROM tipe_lembur");
        return $result->result();
    }

    function data_gaji($thn, $bln, $nik) {
        $data_gaji = $this->db->query(
                "SELECT * FROM data_gaji WHERE nik = ? AND bulan = ? AND tahun = ?", array($nik, $bln, $thn)
        );

        return $data_gaji->row();
    }

    function getJumlahHadir($thn, $bln, $nik) {
        $data_absen = $this->get_scalar("SELECT count(*) as jumlah FROM data_absensi WHERE nik = '" . $nik . "' AND tanggal LIKE '" . $thn . "-" . $bln . "%' and (keterangan = 'N')", "jumlah");
        return $data_absen;
    }

    function getJumlahDinas($thn, $bln, $nik) {
        $data_absen = $this->get_scalar("SELECT count(*) as jumlah FROM data_absensi WHERE nik = '" . $nik . "' AND tanggal LIKE '" . $thn . "-" . $bln . "%' and (keterangan = 'D')", "jumlah");
        return $data_absen;
    }

    function check_absen_available($thn_bln, $nik) {
        $data_absen = $this->get_scalar("SELECT nik FROM data_absensi WHERE nik = '" . $nik . "' AND tanggal LIKE '" . $thn_bln . "%' LIMIT 1", "nik");

        return $data_absen != "";
    }

    function check_gaji_available($thn, $bln, $nik) {
        $data_gaji = $this->get_scalar("SELECT id_gaji FROM data_gaji WHERE nik = '" . $nik . "' AND bulan = '" . $bln . "' AND tahun = '" . $thn . "' LIMIT 1", "id_gaji");

        return $data_gaji;
    }

    function insert_absen($nik, $tanggal) {
        $dm = date("w", strtotime($tanggal));
        $is_libur = 0;
        if ($dm == '0') {
            $is_libur = 1;
        } else {
            $tanggal_libur = $this->get_scalar("SELECT tanggal FROM setting_libur WHERE tanggal = '" . $tanggal . "'", "tanggal");
            if ($tanggal == $tanggal_libur) {
                $is_libur = 1;
            }
        }

        $this->db->insert("data_absensi", array("nik" => $nik, "tanggal" => $tanggal, "is_libur" => $is_libur));
    }

    function update_absen($nik, $tanggal, $keterangan) {
        return $this->db->update("data_absensi", array("keterangan" => $keterangan), array("nik" => $nik, "tanggal" => $tanggal));
    }

    function update_lembur($nik, $tanggal, $tipe) {
        return $this->db->update("data_absensi", array("tipe_lembur" => $tipe), array("nik" => $nik, "tanggal" => $tanggal));
    }

    function getNamaKaryawan($nik) {
        $data_absen = $this->get_scalar("SELECT nama FROM mt_karyawan WHERE nik = '" . $nik . "'", "nama");

        return $data_absen;
    }

    function get_rekap_gaji_abk($thn, $bln) {
        $sql = "select 
            v.nama_vessel, k.nama, k.jabatan,k.norek_an, k.norek_bank,k.norek,d.gaji_pokok,d.tunjangan_jabatan,d.gaji_pokok+d.tunjangan_jabatan as total
            from mt_karyawan k
            left join data_gaji d on k.nik = d.nik
            left join master_vessel v on v.id_vessel = k.id_group_vessel
            where d.tahun = '$thn' and d.bulan = '$bln' order by v.nama_vessel";
        $data_gaji = $this->db->query($sql);
        return $data_gaji->result();
    }

    function get_rekap_absensi($thn, $bln) {
        $data_gaji = $this->db->query(
                "SELECT date_format(d.tanggal,'%Y-%m') as bulan,
            d.nik,
            k.nama,
            case d.keterangan
                when 'S' then 'SAKIT'
                when 'I' then 'IJIN'
                when 'M' then 'MANGKIR'
                when 'D' then 'DINAS'
                when 'C' then 'CUTI'
                when 'N' then 'NORMAL'
                when 'T' then 'TERLAMBAT'
                END as keterangan,
            count(d.nik) as jumlah FROM `data_absensi` d join mt_karyawan k on k.nik = d.nik
            where date_format(d.tanggal,'%Y-%m') = '" . $thn . "-" . $bln . "'
            group by 1,2,3,4
            order by 1"
        );
        $data = array();
        $result = $data_gaji->result();
        foreach ($result as $row) {
            $data[$row->nik]["nama"] = $row->nama;
            $data[$row->nik]["nik"] = $row->nik;
            $data[$row->nik][$row->keterangan] = $row->jumlah;
        }
        return $data;
    }

}

?>